import sqlite3 from 'sqlite3';
import pg from 'pg';
import { fileURLToPath } from 'url';
import path from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

class UnifiedDatabase {
  constructor() {
    this.dbType = 'unknown';
    this.db = null;
    this.isConnected = false;
    this.fallbackEnabled = false;
  }

  // 尝试连接PostgreSQL，失败时使用SQLite
  async connect() {
    console.log('🔗 尝试连接PostgreSQL数据库...');
    
    try {
      // 首先尝试PostgreSQL
      await this.connectPostgreSQL();
      this.dbType = 'postgresql';
      console.log('✅ 使用PostgreSQL数据库');
      return this;
    } catch (pgError) {
      console.warn('⚠️ PostgreSQL连接失败:', pgError.message);
      console.log('💡 切换到SQLite数据库...');
      
      try {
        await this.connectSQLite();
        this.dbType = 'sqlite';
        this.fallbackEnabled = true;
        console.log('✅ 使用SQLite数据库（备用）');
        return this;
      } catch (sqliteError) {
        console.error('❌ 所有数据库连接都失败:', sqliteError.message);
        throw new Error('无法连接任何数据库');
      }
    }
  }

  // 连接PostgreSQL
  async connectPostgreSQL() {
    return new Promise((resolve, reject) => {
      const pool = new pg.Pool({
        host: process.env.DB_HOST || 'localhost',
        port: parseInt(process.env.DB_PORT) || 5432,
        database: process.env.DB_NAME || 'bike_wholesale',
        user: process.env.DB_USER || 'postgres',
        password: process.env.DB_PASSWORD || 'password',
        connectionTimeoutMillis: 5000,
        idleTimeoutMillis: 30000
      });

      pool.connect((err, client, release) => {
        if (err) {
          reject(err);
        } else {
          this.db = pool;
          this.isConnected = true;
          release();
          resolve(this);
        }
      });
    });
  }

  // 连接SQLite
  async connectSQLite() {
    return new Promise((resolve, reject) => {
      const DB_PATH = path.join(__dirname, '..', '..', 'data', 'bike_wholesale.db');
      
      // 确保data目录存在
      const fs = require('fs');
      const dataDir = path.dirname(DB_PATH);
      if (!fs.existsSync(dataDir)) {
        fs.mkdirSync(dataDir, { recursive: true });
      }

      const db = new sqlite3.Database(DB_PATH, (err) => {
        if (err) {
          reject(err);
        } else {
          this.db = db;
          this.isConnected = true;
          resolve(this);
        }
      });
    });
  }

  // 统一查询接口
  async query(sql, params = []) {
    if (!this.isConnected) {
      throw new Error('数据库未连接');
    }

    try {
      if (this.dbType === 'postgresql') {
        return await this.db.query(sql, params);
      } else if (this.dbType === 'sqlite') {
        return await this.sqliteQuery(sql, params);
      }
    } catch (error) {
      console.error('数据库查询错误:', error);
      throw error;
    }
  }

  // SQLite查询封装
  async sqliteQuery(sql, params = []) {
    return new Promise((resolve, reject) => {
      if (sql.trim().toUpperCase().startsWith('SELECT')) {
        this.db.all(sql, params, (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve({ rows, rowCount: rows.length });
          }
        });
      } else {
        this.db.run(sql, params, function(err) {
          if (err) {
            reject(err);
          } else {
            resolve({ rowCount: this.changes, rows: [] });
          }
        });
      }
    });
  }

  // 测试连接
  async testConnection() {
    try {
      const result = await this.query('SELECT 1 as test');
      return result.rows[0].test === 1;
    } catch (error) {
      console.error('数据库连接测试失败:', error.message);
      return false;
    }
  }

  // 初始化数据库
  async initialize() {
    if (this.dbType === 'sqlite') {
      await this.initializeSQLite();
    }
    // PostgreSQL表结构由迁移脚本处理
  }

  // 初始化SQLite表结构
  async initializeSQLite() {
    const tables = [
      `CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT DEFAULT 'user',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        price DECIMAL(10,2) NOT NULL,
        category TEXT,
        stock INTEGER DEFAULT 0,
        image_url TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`
    ];

    for (const tableSql of tables) {
      await this.query(tableSql);
    }
    
    console.log('✅ 数据库表结构初始化完成');
  }

  // 关闭连接
  async close() {
    if (this.db) {
      if (this.dbType === 'postgresql') {
        await this.db.end();
      } else if (this.dbType === 'sqlite') {
        this.db.close();
      }
      this.isConnected = false;
      console.log('✅ 数据库连接已关闭');
    }
  }
}

// 创建全局数据库实例
const unifiedDB = new UnifiedDatabase();

export default unifiedDB;